package com.cpi.cornpp.productpurchase.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.cpi.cornpp.common.exception.CornPPException;
import com.cpi.cornpp.period.exception.PeriodException;
import com.cpi.cornpp.product.exception.ProductException;
import com.cpi.cornpp.productpurchase.dto.ProductPurchaseDTO;
import com.cpi.cornpp.productpurchase.exception.ProductPurchaseException;
import com.cs.common.pool.DBDAO;

public class ProductPurchaseDAO extends DBDAO{

	private static final String DATASOURCE 	= "cornpp";//$NON-NLS-1$
	
	/** SQL List Product  */ 
	private static final String SQL_LIST_PRODUCT_PURCHASE = "SELECT corn_future_distribution_id, id.item_id,quantity,description FROM items_distributions id, vw_items WHERE vw_items.item_id = id.item_id and corn_future_distribution_id = ? "; //$NON-NLS-1$
		
	/** SQL Inserts new Product */ 
	private static final String SQL_INSERT_PRODUCT_PURCHASE = 
		"INSERT INTO items_distributions (	 corn_future_distribution_id , item_id , quantity )"
		+ " VALUES (?,?,?);"; //$NON-NLS-1$
	
	/** SQL delete ProductPurchases by corn_future_distribution_id*/
	private static final String SQL_DELETE_PRODUCT_PURCHASE_BY_FUTURE_DISTRIBUTION_ID = "DELETE FROM items_distributions WHERE corn_future_distribution_id = ?";

	/**
	 * Obtener una lista de ProductPurchase
	 * @param entity
	 * @return
	 * @throws Exception
	 */
	public List list(ProductPurchaseDTO entity) throws Exception {
		ProductPurchaseDTO productPurchaseDTO = null;
		ArrayList arrayResult = null;		
		PreparedStatement preparedStatement =  null;
		try {
			arrayResult = new ArrayList(0);
			preparedStatement =  prepareSQL(SQL_LIST_PRODUCT_PURCHASE, DATASOURCE);
			preparedStatement.setInt(1, entity.getCornFutureDistributionId());
			ResultSet rst = executeQuery(preparedStatement);
			while (rst.next()){
				productPurchaseDTO = new ProductPurchaseDTO();
				productPurchaseDTO.setProductId(rst.getString(2));
				productPurchaseDTO.setDescription(rst.getString(4));
				productPurchaseDTO.setQuantity(rst.getInt(3));
				
				arrayResult.add(productPurchaseDTO);
			}
		}
		catch (SQLException sqlException){
			ProductException productException = null;
			sqlException.printStackTrace(System.out);
			productException= new ProductException(sqlException , CornPPException.LAYER_DAO,CornPPException.ACTION_LISTS);
			throw productException;
		}finally {
			try{
				closeConnection(preparedStatement.getConnection());
			}catch(Exception exception){
			}
		}
		return arrayResult;
	}
	
	/**
	 * 
	 * @param entity
	 * @return
	 * @throws Exception
	 */
	public boolean insert(ProductPurchaseDTO entity) throws Exception {
		PreparedStatement preparedStatement =  null;
		boolean result = false;
		int exInsert = 0;
		try {
			preparedStatement =  prepareSQL(SQL_INSERT_PRODUCT_PURCHASE, DATASOURCE);
			preparedStatement.setInt(1, entity.getCornFutureDistributionId());
			preparedStatement.setString(2, entity.getProductId());
			preparedStatement.setDouble(3, entity.getQuantity());
			
			exInsert = executeInsert(preparedStatement);
			if (exInsert <= 0 ) {
				PeriodException periodException = null;
				periodException = new PeriodException (MSG_NO_INSERT,CornPPException.LAYER_DAO,CornPPException.ACTION_INSERT);
				result=false;
				throw periodException;				
			}
			result=true;
		}
		catch (SQLException sqlException){
			ProductPurchaseException productPurchaseException = null;
			sqlException.printStackTrace(System.out);
			productPurchaseException = new ProductPurchaseException (sqlException , CornPPException.LAYER_DAO,CornPPException.ACTION_INSERT);
			throw productPurchaseException;
		}finally {
			try{
				closeConnection(preparedStatement.getConnection());
			}catch(Exception exception){
			}
		}
		return result;
	}	
	
	/**
	 * Borrar ProductPurchases basados en su corn_future_distribution_id
	 */
	public boolean delete(int cornFutureDistributionId) throws Exception {
		PreparedStatement preparedStatement = null;
		boolean result = false;
		//int exDelete = 0;
		try {
			preparedStatement = prepareSQL(SQL_DELETE_PRODUCT_PURCHASE_BY_FUTURE_DISTRIBUTION_ID, DATASOURCE);
			preparedStatement.setInt(1, cornFutureDistributionId);
			//exDelete = executeUpdate(preparedStatement);
			executeUpdate(preparedStatement);
			/*if (exDelete <= 0) {
				ProductPurchaseException ppException = null;
				ppException = new ProductPurchaseException(MSG_NO_DELETE, ProductPurchaseException.LAYER_DAO,ProductPurchaseException.ACTION_DELETE);
				result = false;
				throw ppException;
			}*/
			result = true;
		} catch (SQLException sqlException) {
			ProductPurchaseException ppException = null;
			sqlException.printStackTrace(System.out);
			ppException = new ProductPurchaseException(sqlException,ProductPurchaseException.LAYER_DAO,	ProductPurchaseException.ACTION_DELETE);
			throw ppException;
		}finally {
			try{
				closeConnection(preparedStatement.getConnection());
			}catch(Exception exception){
			}
		}
		return result;
	}
	
}
